package com.iMusic.businessLogicLayer;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.iMusic.integrationLayer.DBConnection;
import com.iMusic.integrationLayer.DBConnectionManager;

public class CustomerReport {

	private String startDate = null;
	private String endDate = null;
	private DBConnectionManager dbConnMgr = null;
	private DBConnection dbConn = null;
	
	private List<ProductPerCustomerReport> prodPerCust = null;
	private List<VipConsumption> vipConsumption = null;
	private List<BestSaleProduct> bestSaleProduct = null;
	private List<Feedback> feedbacks = null;
	
	
	public CustomerReport(String startDate, String endDate) {
		this.startDate = startDate;
		this.endDate = endDate;
		dbConnMgr = DBConnectionManager.getDBConnectionManager();
		dbConn = dbConnMgr.getDBConnection();;
		dbConn.connect();
	}
	
	
	/*
	 * Generates products sold per customer (for reporting)
	 */
	public void generateProductsPerCustomer() throws SQLException {
		
		String stmt = "SELECT YEAR(curdate())-YEAR(A.Birthday) Age, SUM(OC.Price) SumPrice, A.Username, A.Sex, A.VIP, PT.TypeName, CT.CategoryName, COUNT(OC.OrderContentId) CountItems " +
						"FROM OrderProduct OP, Account A, OrderContent OC, Product P, ProductType PT, CategoryType CT " +
						"WHERE OP.AccountId = A.AccountId " +
						"AND OP.OrderID = OC.OrderId " +
						"AND OC.ProductId = P.ProductId " +
						"AND P.TypeId = PT.TypeID " +
						"AND P.CategoryId = CT.CategoryId " +
						"AND OP.Payed = true " +
						"AND OP.DateCreated between '"+ startDate +"' and '"+ endDate +"' " +
						"GROUP BY Age, A.Sex, A.Username, PT.TypeName, CT.CategoryName " +
						"ORDER BY Age asc, SumPrice desc ";

		ResultSet rs = dbConn.query(stmt);
		
		prodPerCust = new ArrayList<ProductPerCustomerReport>();
		
		while (rs != null && rs.next()) {
			ProductPerCustomerReport item = new ProductPerCustomerReport();
			item.setAge(rs.getInt("Age"));
			item.setPrice(rs.getFloat("SumPrice"));
			item.setUsername(rs.getString("A.Username"));
			item.setSex(rs.getString("A.Sex"));
			item.setVip(rs.getString("A.VIP"));
			item.setTypeName(rs.getString("PT.TypeName"));
			item.setCategoryName(rs.getString("CT.CategoryName"));
			item.setCountItems(rs.getInt("CountItems"));
			
			prodPerCust.add(item);
		}
		
	}
	
	/*
	 * Generates VIP Consumption (for reporting)
	 */
	public void generateVIPConsumptions() throws SQLException {
		
		String stmt = "SELECT SUM(OC.Price) SumPrice, A.Username, A.VIP, COUNT(OC.OrderContentId) CountItems " +
						"FROM OrderProduct OP, Account A, OrderContent OC, Product P, ProductType PT, CategoryType CT " +
						"WHERE OP.AccountId = A.AccountId " +
						"AND OP.OrderID = OC.OrderId " +
						"AND OC.ProductId = P.ProductId " +
						"AND P.TypeId = PT.TypeID " +
						"AND P.CategoryId = CT.CategoryId " +
						"AND OP.Payed = true " +
						"AND A.VIP = 'on' " +
						"AND OP.DateCreated between '"+ startDate +"' and '"+ endDate +"' " +
						"GROUP BY A.Username " +
						"ORDER BY SumPrice desc";
		
		ResultSet rs = dbConn.query(stmt);
		
		vipConsumption = new ArrayList<VipConsumption>();
		
		while (rs != null && rs.next()) {
			VipConsumption item = new VipConsumption();
			item.setPrice(rs.getFloat("SumPrice"));
			item.setUsername(rs.getString("A.Username"));
			item.setCountItems(rs.getInt("CountItems"));
			
			vipConsumption.add(item);
		}
	}
	
	/*
	 * Generates Best Sale Product information (for reporting)
	 */
	public void generateBestSaleProducts() throws SQLException {
		
		String stmt = "SELECT SUM(OC.Price) SumPrice, COUNT(OC.OrderContentId) CountItems, P.ProductId, P.ProductName, PT.TypeName, CT.CategoryName " +
						"FROM OrderProduct OP, OrderContent OC, Product P, ProductType PT, CategoryType CT " +
						"WHERE OP.OrderID = OC.OrderId " +
						"AND OC.ProductId = P.ProductId " +
						"AND P.TypeId = PT.TypeID " +
						"AND P.CategoryId = CT.CategoryId " +
						"AND OP.Payed = true " +
						"AND OP.DateCreated between '"+ startDate +"' and '"+ endDate +"' " +
						"GROUP BY P.ProductName, PT.TypeName, CT.CategoryName " +
						"ORDER BY SumPrice desc";
		
		ResultSet rs = dbConn.query(stmt);
		
		bestSaleProduct = new ArrayList<BestSaleProduct>();
		
		while (rs != null && rs.next()) {
			BestSaleProduct item = new BestSaleProduct();
			item.setPrice(rs.getFloat("SumPrice"));
			item.setCountItems(rs.getInt("CountItems"));
			item.setProductId(rs.getInt("P.ProductId"));
			item.setProductName(rs.getString("P.ProductName"));
			item.setTypeName(rs.getString("PT.TypeName"));
			item.setCategoryName(rs.getString("CT.CategoryName"));
			
			bestSaleProduct.add(item);
		}
		
	}
	
	
	/*
	 * Generates Feedbacks
	 */
	public void generateFeedbacks() throws SQLException {
		
		String stmt = "SELECT FeedbackId, Description, DateCreated " +
						"FROM Feedback " +
						"WHERE DateCreated between '"+ startDate +"' and '"+ endDate +"' " +
						"ORDER BY DateCreated";
		
		ResultSet rs = dbConn.query(stmt);
		
		feedbacks = new ArrayList<Feedback>();
		
		while (rs != null && rs.next()) {
			Feedback item = new Feedback();
			item.setFeedbackId(rs.getInt("FeedbackId"));
			item.setDescription(rs.getString("Description"));
			item.setDateCreated(rs.getDate("DateCreated"));
			
			feedbacks.add(item);
		}
		
	}
	protected void finalize() {
		dbConn.disconnect();
		dbConnMgr.freeDBConnection(dbConn);
	}

	public List<ProductPerCustomerReport> getProdPerCust() {
		return prodPerCust;
	}

	public void setProdPerCust(List<ProductPerCustomerReport> prodPerCust) {
		this.prodPerCust = prodPerCust;
	}


	public List<VipConsumption> getVipConsumption() {
		return vipConsumption;
	}


	public void setVipConsumption(List<VipConsumption> vipConsumption) {
		this.vipConsumption = vipConsumption;
	}


	public List<BestSaleProduct> getBestSaleProduct() {
		return bestSaleProduct;
	}


	public void setBestSaleProduct(List<BestSaleProduct> bestSaleProduct) {
		this.bestSaleProduct = bestSaleProduct;
	}


	public List<Feedback> getFeedbacks() {
		return feedbacks;
	}


	public void setFeedbacks(List<Feedback> feedbacks) {
		this.feedbacks = feedbacks;
	}
}
